package com.jcp.daoimp;

import java.util.ArrayList;
import java.util.List;

import com.jcp.dao.FilesDao;
import com.jcp.entity.AddressBook;
import com.jcp.entity.Files;
import com.jcp.entity.FilesAndUserName;
import com.jcp.entity.FilesAndUsers;
import com.jcp.entity.FilesMin;
import com.jcp.util.DBUtil;
import com.jcp.util.PageData;

public class FilesDaoImp implements FilesDao {

	@Override
	/**
	 * 获得通讯录的方法
	 * return PageData<AddressBook> 通讯录列表
	 */
	public PageData<AddressBook> selUserPhoneByPage(int page, int pageSize, String keyword) {
		// TODO Auto-generated method stub
		String sql="select users.userName,files.phone,departments.deptName from files inner join users on files.cardId=users.cardId inner join departments on users.deptId=departments.deptId where users.userName like ? or files.phone like ?";
		String sql2="select users.userName,files.phone from users inner join files on users.cardId=files.cardId where userId=(select deptManId from departments where deptName=?)";
		PageData<AddressBook> pagedata = DBUtil.exQueryByPage(sql, AddressBook.class, page, pageSize, "%"+keyword+"%","%"+keyword+"%");
		List<AddressBook> list =pagedata.getData();
		List<AddressBook> list2=new ArrayList<>();
		for (AddressBook addressBook : list) {
			String deptName=addressBook.getDeptName();
			List<AddressBook> list3 = DBUtil.exQuery(sql2, AddressBook.class, deptName);
			String deptManName=list3.get(0).getUserName();
			String deptManPhone=list3.get(0).getPhone();
			addressBook.setDeptManName(deptManName);
			addressBook.setDeptManPhone(deptManPhone);
			list2.add(addressBook);
		}
		pagedata.setData(list2);
		return pagedata;
	}
	
	@Override
	/**
	 * 添加档案的方法
	 * return boolean 添加是否成功
	 */
	public boolean addFiles(Files files) {
		// TODO Auto-generated method stub
		String sql="insert into files(files.sex,files.nation,files.address,\r\n" + 
				"files.birthday,files.cardId,files.political,\r\n" + 
				"files.isMarried,files.school,files.graduateTime,\r\n" + 
				"files.major,files.cv1,files.cv2,files.cv3,files.phone,files.blood,\r\n" + 
				"files.weight,files.height,files.education,files.photosurl )VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		return DBUtil.exUpdate(sql,files.getSex(),files.getNation(),files.getAddress(),files.getBirthday(),files.getCardId(),
				files.getPolitical(),files.getIsMarried(),files.getSchool(),files.getGraduateTime(),files.getMajor(),
				files.getCv1(),files.getCv2(),files.getCv3(),files.getPhone(),files.getBlood(),files.getWeight(),files.getHeight(),
				files.getEducation(),files.getPhotosurl())>0;
	}

	@Override
	/**
	 * 删除档案的方法
	 * return boolean 删除是否成功
	 */
	public boolean delFiles(int fileId) {
		// TODO Auto-generated method stub
		String sql="DELETE from files where files.fileId=?";
		return DBUtil.exUpdate(sql, fileId)>0;
	}

	@Override
	/**
	 * 更新档案的方法
	 * return boolean 更新是否成功
	 */
	public boolean updFiles(Files files) {
		// TODO Auto-generated method stub
		String sql="update files set files.sex=?,files.nation=?,files.address=?,files.birthday=?,files.cardId=?,files.political=?,files.isMarried=?,files.school=?,files.graduateTime=?,files.major=?,files.cv1=?,files.cv2=?,files.cv3=?,files.phone=?,files.blood=?,files.weight=?,files.height=?,files.education=?,files.photosurl=? where files.fileId=?";
		return DBUtil.exUpdate(sql,files.getSex(),files.getNation(),files.getAddress(),files.getBirthday(),files.getCardId(),files.getPolitical(),files.getIsMarried(),files.getSchool(),files.getGraduateTime(),files.getMajor(),
				files.getCv1(),files.getCv2(),files.getCv3(),files.getPhone(),files.getBlood(),files.getWeight(),files.getHeight(),
				files.getEducation(),files.getPhotosurl(),files.getFileId())>0;
	}

	@Override
	/**
	 * 查询档案的方法
	 * return List<FilesAndUserName> 返回一个list的合集
	 */
	public List<FilesAndUserName> selFiles(int filesIdd) {
		// TODO Auto-generated method stub
		String sql ="SELECT files.fileId,users.userName,files.sex,files.nation,files.address,files.birthday,files.cardId,files.political,files.isMarried,files.school,files.graduateTime,files.major,files.cv1,files.cv2,files.cv3,files.phone,files.blood,\r\n" + 
				"files.weight,files.height,files.education,files.photosurl from files \r\n" + 
				"INNER JOIN users on files.cardId=users.cardId where files.fileId=? ";
		@SuppressWarnings("unchecked")
		List<FilesAndUserName> list = DBUtil.exQuery(sql, FilesAndUserName.class,filesIdd);
		return list;
	}

	@Override
	/**
	 * 查询min档案的方法
	 * return List<FilesMin> 返回一个list的合集
	 */
	public List<FilesMin> selFilesmin() {
		// TODO Auto-generated method stub
		String sql="SELECT files.fileId,users.userName,files.cardId,files.phone  from files \r\n" + 
				"INNER JOIN users \r\n" + 
				"on files.cardId=users.cardId;";
		List<FilesMin> list=DBUtil.exQuery(sql,FilesMin.class);
		return list;
	}

	@Override
	/**
	 * 分页查询档案的方法
	 * return List<FilesMIn> 返回一个list的合集
	 */
	public PageData<FilesMin> getselFilesminMinPage(int page, int pageSize) {
		// TODO Auto-generated method stub
		String sql="SELECT files.fileId,users.userName,files.cardId,files.phone  from files \r\n" + 
				"INNER JOIN users \r\n" + 
				"on files.cardId=users.cardId";
		return DBUtil.exQueryByPage(sql, FilesMin.class, page, pageSize);
	}

	@Override
	/**
	 * 分页模糊查询档案的方法
	 * return List<FilesAndUserName> 返回一个list的合集
	 */
	public PageData<FilesMin> getselFilesminMinPage1(int page, int pageSize, String strLike) {
		// TODO Auto-generated method stub
		String sql="SELECT files.fileId,users.userName,files.cardId,files.phone  from files \r\n" + 
				"INNER JOIN users \r\n" + 
				"on files.cardId=users.cardId\r\n" + 
				"where users.userName like ?";
		return DBUtil.exQueryByPage(sql, FilesMin.class, page, pageSize, "%"+strLike+"%");
	}
	@Override
	public List<FilesAndUsers> Filesel(String strLike) {
		String sql = "SELECT temp1.userId,temp1.userName,temp1.sex,temp1.phone,temp1.userEmail,temp1.address,temp1.cardId,d.deptName from(select u.userId,u.userName,f.sex,f.phone,u.userEmail,f.address,f.cardId,u.deptId from files f inner join users u on f.cardId=u.cardId) as temp1 INNER JOIN departments d ON temp1.deptId=d.deptId where userName like ?";
		List<FilesAndUsers> list = DBUtil.exQuery(sql, FilesAndUsers.class, "%"+strLike+"%");
		return list;
	}
	
	
	
	
	
}
